package classes;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Ndnam
 */
public class AuctionDAO {

    private Connection con = null;

    public AuctionDAO() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=AuctionDB;user=sa;password=sa");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<Shipment> getShipmentTable() {
        ArrayList<Shipment> list = new ArrayList<Shipment>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From Shipment");
            while (rs.next()) {
                Shipment shipment = new Shipment(rs.getInt(1), rs.getString(2));
                list.add(shipment);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public ArrayList<Auction> getAuctionTable() {
        ArrayList<Auction> list = new ArrayList<Auction>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From Auction");
            while (rs.next()) {
                Auction auction = new Auction(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5),
                        rs.getFloat(6), rs.getFloat(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
                        rs.getString(12), rs.getInt(13), rs.getInt(14), rs.getString(15), rs.getString(16));
                list.add(auction);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public ArrayList<Bid> getBidTable() {
        ArrayList<Bid> list = new ArrayList<Bid>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From BidList");
            while (rs.next()) {
                Bid bid = new Bid(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getFloat(4), rs.getString(5), rs.getString(6));
                list.add(bid);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public ArrayList<ProductType> getProductTypeTable() {
        ArrayList<ProductType> list = new ArrayList<ProductType>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From ProductType");
            while (rs.next()) {
                ProductType productType = new ProductType(rs.getInt(1), rs.getString(2));
                list.add(productType);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public ArrayList<User> getUserTable() {
        ArrayList<User> list = new ArrayList<User>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From [User]");
            while (rs.next()) {
                User user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13));
                list.add(user);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }

    public void insertAuction(Auction auction) throws SQLException { //Return false if unsuccess
        String insertStatement = "INSERT INTO Auction (Creator,LastBidder,ProductType,ProductName,InitialPrice,LastPrice,BeginTime,BeginDate,EndTime,EndDate,State,PaymentTypes,ShipmentTypes,ImageURL,VideoURL) VALUES ("
                + auction.getCreator() + ","
                + (auction.getLastbidder() == 0 ? "NULL" : auction.getLastbidder()) + ","
                + auction.getProductType() + ",'"
                + auction.getProductName() + "',"
                + auction.getInitialPrice() + ","
                + auction.getLastPrice() + ",'"
                + auction.getBeginTime() + "','"
                + auction.getBeginDate() + "','"
                + auction.getEndTime() + "','"
                + auction.getEndDate() + "','"
                + auction.getState() + "',"
                + auction.getPaymentTypes() + ","
                + auction.getShipmentTypes() + ",'"
                + auction.getImageURL() + "','"
                + auction.getVideoURL() + "');";
        Statement stmt = con.createStatement();
        stmt.execute(insertStatement);
    }

    public void insertBid(Bid bid) throws SQLException {
        String insertStatement = "INSERT INTO BidList (AuctionID,BidderID,PlacedPrice,State,BidTime) VALUES ("
                + bid.getAuctionID() + ","
                + bid.getBidderID() + ","
                + bid.getPlacedPrice() + ",'"
                + bid.getState() + "','"
                + bid.getBidTime() + "');";
        Statement stmt = con.createStatement();
        stmt.execute(insertStatement);
    }

    public void insertProductType(ProductType productType) throws SQLException {
        String insertStatement = "INSERT INTO ProductType " + "VALUES ("
                + productType.getTypeID() + ",'"
                + productType.getType() + "');";
        Statement stmt = con.createStatement();
        stmt.execute(insertStatement);
    }

    public void insertUser(User user) throws SQLException {
        String insertStatement = "INSERT INTO [User] (Username,Firstname,Lastname,Email,Password,UserType,Address1,Address2,State,ZipCode,Country,PhoneNumber)\n VALUES ('"
                + user.getUsername().toLowerCase() + "','"
                + user.getFirstname() + "','"
                + user.getLastname() + "','"
                + user.getEmail() + "','"
                + user.getPassword() + "','"
                + user.getUsertype() + "','"
                + user.getAddress1() + "','"
                + user.getAddress2() + "','"
                + user.getState() + "','"
                + user.getZipcode() + "','"
                + user.getCountry() + "','"
                + user.getPhonenumber() + "');";
        Statement stmt = con.createStatement();
        stmt.execute(insertStatement);
    }

    public void updateAuction(Auction auction) throws SQLException {
        String updateStatement = "UPDATE Auction SET "
                + "Creator = " + auction.getCreator() + ", "
                + "LastBidder = " + auction.getLastbidder() + ", "
                + "ProductType = " + auction.getProductType() + ", "
                + "ProductName = '" + auction.getProductName() + "', "
                + "InitialPrice = " + auction.getInitialPrice() + ", "
                + "LastPrice = " + auction.getLastPrice() + ", "
                + "BeginTime = '" + auction.getBeginTime() + "', "
                + "BeginDate = '" + auction.getBeginDate() + "', "
                + "EndTime = '" + auction.getEndTime() + "', "
                + "EndDate = '" + auction.getEndDate() + "', "
                + "State = '" + auction.getState() + "', "
                + "PaymentTypes = " + auction.getPaymentTypes() + ", "
                + "ShipmentTypes = " + auction.getShipmentTypes() + ", "
                + "ImageURL = '" + auction.getImageURL() + "', "
                + "VideoURL = '" + auction.getVideoURL() + "'"
                + " WHERE AuctionID = " + auction.getAuctionID() + ";";
        Statement stmt = con.createStatement();
        stmt.execute(updateStatement);
    }

    public void updateBid(Bid bid) throws SQLException {
        String updateStatement = "UPDATE BidList SET "
                + "AuctionID = " + bid.getAuctionID() + ", "
                + "BidderID = " + bid.getBidderID() + ", "
                + "PlacedPrice = " + bid.getPlacedPrice() + ", "
                + "State = '" + bid.getState() + "',"
                + "BidTime = '" + bid.getBidTime() + "'"
                + " WHERE BidID = " + bid.getBidID() + ";";
        Statement stmt = con.createStatement();
        stmt.execute(updateStatement);
    }

    public void updateProductType(ProductType productType) throws SQLException {
        String updateStatement = "UPDATE ProductType SET "
                + "Type = '" + productType.getType() + "'"
                + " WHERE TypeID = " + productType.getTypeID() + ";";
        Statement stmt = con.createStatement();
        stmt.execute(updateStatement);
    }

    public void updateUser(User user) throws SQLException {
        String updateStatement = "UPDATE [User] SET "
                + "Username = '" + user.getUsername() + "',"
                + "Firstname = '" + user.getFirstname() + "',"
                + "Lastname = '" + user.getLastname() + "',"
                + "Email = '" + user.getEmail() + "',"
                + "Password = '" + user.getPassword() + "',"
                + "UserType = '" + user.getUsertype() + "',"
                + "Address1 = '" + user.getAddress1() + "',"
                + "Address2 = '" + user.getAddress2() + "',"
                + "State = '" + user.getState() + "',"
                + "Zipcode = '" + user.getZipcode() + "',"
                + "Country = '" + user.getCountry() + "',"
                + "Phonenumber = '" + user.getPhonenumber() + "'"
                + " WHERE UserID = " + user.getUserID() + ";";
        Statement stmt = con.createStatement();
        stmt.execute(updateStatement);
    }

    public void deleteAuction(int auctionID) throws SQLException {
        Statement stmt = con.createStatement();
        stmt.execute("DELETE FROM Auction WHERE AuctionID = " + auctionID + ";");
    }

    public void deleteBid(int bidID) throws SQLException {
        Statement stmt = con.createStatement();
        stmt.execute("DELETE FROM BidList WHERE BidID = " + bidID + ";");
    }

    public void deleteProductType(int typeID) throws SQLException {
        Statement stmt = con.createStatement();
        stmt.execute("DELETE FROM ProductType WHERE TypeID = " + typeID + ";");
    }

    public void deleteUser(int userID) throws SQLException {
        Statement stmt = con.createStatement();
        stmt.execute("DELETE FROM [User] WHERE UserID = " + userID + ";");
    }

    public User searchUserByUsername(String username) {
        User user = null;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From [User] WHERE Username = '" + username + "'");
            if (rs.next()) {
                user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }

    public User searchUserByEmail(String email) {
        User user = null;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From [User] WHERE Email = '" + email + "'");
            if (rs.next()) {
                user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }

    public User searchUserByID(int id) {
        User user = null;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From [User] WHERE UserID = " + id);
            if (rs.next()) {
                user = new User(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),
                        rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;
    }

    public ProductType searchProductTypeByID(int id) {
        ProductType productType = null;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From ProductType WHERE TypeID = " + id);
            if (rs.next()) {
                productType = new ProductType(rs.getInt(1), rs.getString(2));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return productType;
    }

    public Auction searchAuctionByID(int id) {
        Auction auction = null;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * From Auction WHERE AuctionID = " + id);
            if (rs.next()) {
                auction = new Auction(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5),
                        rs.getFloat(6), rs.getFloat(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
                        rs.getString(12), rs.getInt(13), rs.getInt(14), rs.getString(15), rs.getString(16));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AuctionDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return auction;
    }

    public ArrayList<Auction> searchAuctionByProductName(String keyword) throws SQLException {
        ArrayList<Auction> list = new ArrayList<Auction>();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * From Auction WHERE ProductName LIKE '%" + keyword + "%';");
        while (rs.next()) {
            Auction auction = new Auction(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5),
                    rs.getFloat(6), rs.getFloat(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
                    rs.getString(12), rs.getInt(13), rs.getInt(14), rs.getString(15), rs.getString(16));
            list.add(auction);
        }
        return list;
    }
}
